// --------------------------------------------------------------------------------------------------------------------
// <copyright file="201203281108398_V2.cs" company="ecblog.codeplex.com">
//   emrecoskun.net - ibrahimatay.com
// </copyright>
// <summary>
//   Db updates
// </summary>
// --------------------------------------------------------------------------------------------------------------------

namespace EcBlog.Migrations
{
    using System;
    using System.Data.Entity.Migrations;
    using System.Data.Entity.Migrations.Builders;
    using System.Data.Entity.Migrations.Model;

    using EcBlog.Data.Sql;
    using EcBlog.Extensions;

    /// <summary>
    /// Db updates
    /// </summary>
    public partial class V2 : DbMigration
    {
        /// <summary>
        /// Operations to be performed during the upgrade process.
        /// </summary>
        public override void Up()
        {
            var context = new EcModelContext();

            if (!context.IsTableExists("CommentHistories"))
            {
                this.CreateTable(
                    "CommentHistories",
                    c =>
                    new
                        {
                            CommentHistoryId = c.Long(nullable: false, identity: true),
                            CommentId = c.Long(nullable: false),
                            UserName = c.String(nullable: false, maxLength: 20),
                            Email = c.String(nullable: false, maxLength: 50),
                            Web = c.String(maxLength: 50),
                            SendResponseMail = c.Boolean(nullable: false),
                            CommentHtml = c.String(nullable: false, maxLength: 4000),
                            PageId = c.Long(nullable: false),
                            IsPublished = c.Boolean(nullable: false),
                            Created = c.DateTime(nullable: false),
                            LastModified = c.DateTime(nullable: false),
                            LastModifiedUser = c.String(maxLength: 50),
                            Operation = c.String(),
                        }).PrimaryKey(t => t.CommentHistoryId);
            }

            if (!context.IsTableExists("LayoutHistories"))
            {
                this.CreateTable(
                    "LayoutHistories",
                    c =>
                    new
                        {
                            LayoutHistoryId = c.Long(nullable: false, identity: true),
                            LayoutId = c.Long(nullable: false),
                            LayoutPath = c.String(nullable: false), 
                            Name = c.String(nullable: false),
                            LastModified = c.DateTime(nullable: false),
                            LastModifiedUser = c.String(maxLength: 50),
                            Operation = c.String(),
                        }).PrimaryKey(t => t.LayoutHistoryId);
            }

            if (!context.IsTableExists("TagHistories"))
            {
                this.CreateTable(
                    "TagHistories",
                    c =>
                    new
                        {
                            TagHistoryId = c.Long(nullable: false, identity: true),
                            TagId = c.Long(nullable: false),
                            TagName = c.String(nullable: false, maxLength: 50),
                            TagSlug = c.String(nullable: false, maxLength: 50),
                            LastModified = c.DateTime(nullable: false),
                            LastModifiedUser = c.String(maxLength: 50),
                            Operation = c.String(),
                        }).PrimaryKey(t => t.TagHistoryId);
            }

            if (!context.IsTableExists("UserHistories"))
            {
                this.CreateTable(
                    "UserHistories",
                    c =>
                    new
                        {
                            UserHistoryId = c.Long(nullable: false, identity: true),
                            UserId = c.Long(nullable: false),
                            Username = c.String(nullable: false),
                            FullName = c.String(nullable: false, maxLength: 50),
                            Email = c.String(nullable: false, maxLength: 100), 
                            LastModified = c.DateTime(nullable: false),
                            LastModifiedUser = c.String(maxLength: 50),
                            Operation = c.String(),
                        }).PrimaryKey(t => t.UserHistoryId);
            }

            if (!context.IsTableExists("PageHistories"))
            {
                this.CreateTable(
                    "PageHistories",
                    c =>
                    new
                        {
                            PageHistoryId = c.Long(nullable: false, identity: true),
                            PageId = c.Long(nullable: false),
                            Title = c.String(nullable: false, maxLength: 200),
                            Slug = c.String(nullable: false),
                            ContentHtml = c.String(storeType: "ntext"),
                            LayoutId = c.Long(nullable: false),
                            IsCommentable = c.Boolean(nullable: false),
                            IsPublish = c.Boolean(nullable: false),
                            Published = c.DateTime(nullable: false),
                            Created = c.DateTime(nullable: false),
                            ShowPostList = c.Boolean(nullable: false),
                            Summary = c.String(storeType: "ntext"),
                            LastModified = c.DateTime(nullable: false),
                            LastModifiedUser = c.String(maxLength: 50),
                            Operation = c.String(),
                        }).PrimaryKey(t => t.PageHistoryId);
            }

            if (!context.IsTableExists("SiteSettingHistories"))
            {
                this.CreateTable(
                    "SiteSettingHistories",
                    c =>
                    new
                        {
                            SiteSettingHistoryId = c.Long(nullable: false, identity: true),
                            SiteSettingId = c.Long(nullable: false),
                            SettingKey = c.String(maxLength: 50),
                            SettingValue = c.String(maxLength: 1000),
                            LastModified = c.DateTime(nullable: false),
                            LastModifiedUser = c.String(maxLength: 50),
                            Operation = c.String(),
                        }).PrimaryKey(t => t.SiteSettingHistoryId);
            }

            if (!context.IsColumnExists("Comments", "LastModified"))
            {
                this.AddColumn("Comments", "LastModified", c => c.DateTime(nullable: false, defaultValueSql: "GetDate()"));    
            }

            if (!context.IsColumnExists("Comments", "LastModifiedUser"))
            {
                this.AddColumn("Comments", "LastModifiedUser", c => c.String(maxLength: 50));
            }

            if (!context.IsColumnExists("Layouts", "LastModified"))
            {
                this.AddColumn("Layouts", "LastModified", c => c.DateTime(nullable: false, defaultValueSql: "GetDate()"));
            }

            if (!context.IsColumnExists("Layouts", "LastModifiedUser"))
            {
                this.AddColumn("Layouts", "LastModifiedUser", c => c.String(maxLength: 50));
            }

            if (!context.IsColumnExists("Pages", "LastModified"))
            {
                this.AddColumn("Pages", "LastModified", c => c.DateTime(nullable: false, defaultValueSql: "GetDate()"));
            }

            if (!context.IsColumnExists("Pages", "LastModifiedUser"))
            {
                this.AddColumn("Pages", "LastModifiedUser", c => c.String(maxLength: 50));
            }

            if (!context.IsColumnExists("Tags", "LastModified"))
            {
                this.AddColumn("Tags", "LastModified", c => c.DateTime(nullable: false, defaultValueSql: "GetDate()"));
            }

            if (!context.IsColumnExists("Tags", "LastModifiedUser"))
            {
                this.AddColumn("Tags", "LastModifiedUser", c => c.String(maxLength: 50));
            }

            if (!context.IsColumnExists("Users", "LastModified"))
            {
                this.AddColumn("Users", "LastModified", c => c.DateTime(nullable: false, defaultValueSql: "GetDate()"));
            }

            if (!context.IsColumnExists("Users", "LastModifiedUser"))
            {
                this.AddColumn("Users", "LastModifiedUser", c => c.String(maxLength: 50));
            }

            if (!context.IsColumnExists("SiteSettings", "LastModified"))
            {
                this.AddColumn("SiteSettings", "LastModified", c => c.DateTime(nullable: false, defaultValueSql: "GetDate()"));
            }

            if (!context.IsColumnExists("SiteSettings", "LastModifiedUser"))
            {
                this.AddColumn("SiteSettings", "LastModifiedUser", c => c.String(maxLength: 50));
            } 

            this.AlterColumn("Users", "UserName", c => c.String(nullable: false));
            this.AlterColumn("SiteSettings", "SettingKey", c => c.String(maxLength: 50));
            this.AlterColumn("SiteSettings", "SettingValue", c => c.String(maxLength: 1000));
            
            if (context.IsColumnExists("Comments", "LastChanged"))
            {
                this.DropColumn("Comments", "LastChanged");
            }

            if (context.IsColumnExists("Pages", "LastChanged"))
            {
                this.DropColumn("Pages", "LastChanged");
            }
            // Direct column renaming is not supported by SQL Server Compact. 
            // To rename a column in SQL Server Compact, you will need to recreate it.
            // RenameColumn("Comments", "LastChanged", "LastModified");
            // RenameColumn("Pages", "LastChanged", "LastModified");

            // id columns're changing with long size
            if (context.IsIndexExists("TagPages", "Page_PageId"))
            {
                this.DropIndex("TagPages", new[] { "Page_PageId" });
            }

            if (context.IsIndexExists("TagPages", "Tag_TagId"))
            {
                this.DropIndex("TagPages", new[] { "Tag_TagId" });
            } 

            if (context.IsIndexExists("Pages", "LayoutId"))
            {
                this.DropIndex("Pages", new[] { "LayoutId" });
            }

            if (context.IsIndexExists("Comments", "PageId"))
            {
                this.DropIndex("Comments", new[] { "PageId" });
            }

            if (context.IsForeignKeyExists("TagPages", "Page_PageId"))
            {
                this.DropForeignKey("TagPages", context.GetForeignKeyName("TagPages", "Page_PageId"));
            }

            if (context.IsForeignKeyExists("TagPages", "Tag_TagId"))
            {
                this.DropForeignKey("TagPages", context.GetForeignKeyName("TagPages", "Tag_TagId"));
            }
 
            if (context.IsForeignKeyExists("Pages", "LayoutId"))
            {
                this.DropForeignKey("Pages", context.GetForeignKeyName("Pages", "LayoutId"));
            }

            if (context.IsForeignKeyExists("Comments", "PageId"))
            {
                this.DropForeignKey("Comments", context.GetForeignKeyName("Comments", "PageId"));
            }

            if (context.IsForeignKeyExists("TagPages", "PagePageId"))
            {
                this.DropForeignKey("TagPages", context.GetForeignKeyName("TagPages", "PagePageId"));
            }

            if (context.IsForeignKeyExists("TagPages", "TagTagId"))
            {
                this.DropForeignKey("TagPages", context.GetForeignKeyName("TagPages", "TagTagId"));
            }

            if (context.IsPrimaryKeyExists("Comments"))
            {
                this.DropPrimaryKey("Comments", context.GetPrimaryKeyName("Comments"));
            }

            if (context.IsPrimaryKeyExists("SiteSettings"))
            {
                this.DropPrimaryKey("SiteSettings", context.GetPrimaryKeyName("SiteSettings"));
            }
 
            if (context.IsPrimaryKeyExists("TagPages"))
            {
                this.DropPrimaryKey("TagPages", context.GetPrimaryKeyName("TagPages"));
            }

            if (context.IsPrimaryKeyExists("Tags"))
            {
                this.DropPrimaryKey("Tags", context.GetPrimaryKeyName("Tags"));
            }

            if (context.IsPrimaryKeyExists("Layouts"))
            {
                this.DropPrimaryKey("Layouts", context.GetPrimaryKeyName("Layouts"));
            }

            if (context.IsPrimaryKeyExists("Pages"))
            {
                this.DropPrimaryKey("Pages", context.GetPrimaryKeyName("Pages"));
            }

            if (context.IsPrimaryKeyExists("Users"))
            {
                this.DropPrimaryKey("Users", context.GetPrimaryKeyName("Users"));
            }

            if (context.IsColumnExists("TagPages", "TagTagId") && !context.IsColumnExists("TagPages", "Tag_TagId"))
            {
                this.AddColumn("TagPages", "Tag_TagId", c => c.Long(nullable: false));
                this.Sql("UPDATE TagPages SET Tag_TagId = TagTagId");
                this.DropColumn("TagPages", "TagTagId");
            }

            if (context.IsColumnExists("Users", "Username"))
            {
                this.ChangeColumnType("Users", "Username", c => c.String(nullable: false, maxLength: 50), "Substring({0},0,50)");    
            }

            if (context.IsColumnExists("Users", "PortalRole"))
            {
                this.ChangeColumnType("Users", "PortalRole", c => c.String(maxLength: 10), "Substring({0},0,10)");
            }

            if (context.IsColumnExists("SiteSettings", "SettingKey"))
            {
                this.ChangeColumnType("SiteSettings", "SettingKey", c => c.String(maxLength: 50), "Substring({0},0,50)");
            }

            if (context.IsColumnExists("SiteSettings", "SettingValue"))
            {
                this.ChangeColumnType("SiteSettings", "SettingValue", c => c.String(maxLength: 50), "Substring({0},0,50)");
            }

            if (context.IsColumnExists("CommentHistories", "Operation"))
            {
                this.ChangeColumnType("CommentHistories", "Operation", c => c.String(maxLength: 10), "Substring({0},0,10)");
            }

            if (context.IsColumnExists("LayoutHistories", "LayoutPath"))
            {
                this.ChangeColumnType("LayoutHistories", "LayoutPath", c => c.String(nullable: false, maxLength: 100), "Substring({0},0,100)");
            }

            if (context.IsColumnExists("LayoutHistories", "Name"))
            {
                this.ChangeColumnType("LayoutHistories", "Name", c => c.String(nullable: false, maxLength: 100), "Substring({0},0,100)");
            }

            if (context.IsColumnExists("LayoutHistories", "Operation"))
            {
                this.ChangeColumnType("LayoutHistories", "Operation", c => c.String(maxLength: 10), "Substring({0},0,10)");
            }

            if (context.IsColumnExists("TagHistories", "Operation"))
            {
                this.ChangeColumnType("TagHistories", "Operation", c => c.String(maxLength: 10), "Substring({0},0,10)");
            }

            if (context.IsColumnExists("UserHistories", "Username"))
            {
                this.ChangeColumnType("UserHistories", "Username", c => c.String(nullable: false, maxLength: 50), "Substring({0},0,50)");
            }

            if (context.IsColumnExists("UserHistories", "PortalRole"))
            {
                this.ChangeColumnType("UserHistories", "PortalRole", c => c.String(maxLength: 10), "Substring({0},0,10)");
            }

            if (context.IsColumnExists("UserHistories", "Operation"))
            {
                this.ChangeColumnType("UserHistories", "Operation", c => c.String(maxLength: 10), "Substring({0},0,10)");
            }

            if (context.IsColumnExists("PageHistories", "Title"))
            {
                this.ChangeColumnType("PageHistories", "Title", c => c.String(nullable: false, maxLength: 500), "Substring({0},0,500)");
            }

            if (context.IsColumnExists("PageHistories", "Slug"))
            {
                this.ChangeColumnType("PageHistories", "Slug", c => c.String(nullable: false, maxLength: 500), "Substring({0},0,500)");
            }

            if (context.IsColumnExists("PageHistories", "Operation"))
            {
                this.ChangeColumnType("PageHistories", "Operation", c => c.String(maxLength: 10), "Substring({0},0,10)");
            }

            if (context.IsColumnExists("SiteSettingHistories", "SettingKey"))
            {
                this.ChangeColumnType("SiteSettingHistories", "SettingKey", c => c.String(maxLength: 50), "Substring({0},0,50)");
            }

            if (context.IsColumnExists("SiteSettingHistories", "SettingValue"))
            {
                this.ChangeColumnType("SiteSettingHistories", "SettingValue", c => c.String(maxLength: 50), "Substring({0},0,50)");
            }

            if (context.IsColumnExists("SiteSettingHistories", "Operation"))
            {
                this.ChangeColumnType("SiteSettingHistories", "Operation", c => c.String(maxLength: 10), "Substring({0},0,10)");
            }

            if (context.IsColumnExists("Comments", "CommentId"))
            {
                this.AlterColumn("Comments", "CommentId", c => c.Long(nullable: false, identity: true));
            }

            if (context.IsColumnExists("Comments", "PageId"))
            {
                this.AlterColumn("Comments", "PageId", c => c.Long(nullable: false));
            }

            if (context.IsColumnExists("Layouts", "LayoutId"))
            {
                this.AlterColumn("Layouts", "LayoutId", c => c.Long(nullable: false, identity: true));
            }

            if (context.IsColumnExists("Pages", "PageId"))
            {
                this.AlterColumn("Pages", "PageId", c => c.Long(nullable: false, identity: true));
            }

            if (context.IsColumnExists("Pages", "LayoutId"))
            {
                this.AlterColumn("Pages", "LayoutId", c => c.Long(nullable: false));
            }

            if (context.IsColumnExists("SiteSettings", "SiteSettingId"))
            {
                this.AlterColumn("SiteSettings", "SiteSettingId", c => c.Long(nullable: false, identity: true));
            }

            if (context.IsColumnExists("Tags", "TagId"))
            {
                this.AlterColumn("Tags", "TagId", c => c.Long(nullable: false, identity: true));
            }

            if (context.IsColumnExists("TagPages", "Tag_TagId"))
            {
                this.AlterColumn("TagPages", "Tag_TagId", c => c.Long(nullable: false));
            }

            if (context.IsColumnExists("TagPages", "Page_PageId"))
            {
                this.AlterColumn("TagPages", "Page_PageId", c => c.Long(nullable: false));
            }

            if (context.IsColumnExists("Users", "UserId"))
            {
                this.AlterColumn("Users", "UserId", c => c.Long(nullable: false, identity: true));
            }

            if (!context.IsPrimaryKeyExists("Comments"))
            {
                this.AddPrimaryKey("Comments", "CommentId");
            }

            if (!context.IsPrimaryKeyExists("Layouts"))
            {
                this.AddPrimaryKey("Layouts", "LayoutId");
            }

            if (!context.IsPrimaryKeyExists("Pages"))
            {
                this.AddPrimaryKey("Pages", "PageId");
            }

            if (!context.IsPrimaryKeyExists("SiteSettings"))
            {
                this.AddPrimaryKey("SiteSettings", "SiteSettingId");
            }

            if (!context.IsPrimaryKeyExists("Tags"))
            {
                this.AddPrimaryKey("Tags", "TagId");
            }

            if (!context.IsPrimaryKeyExists("Users"))
            {
                this.AddPrimaryKey("Users", "UserId");
            }

            if (!context.IsPrimaryKeyExists("TagPages"))
            {
                this.AddPrimaryKey("TagPages", new[] { "Tag_TagId", "Page_PageId" });
            }

            if (!context.IsForeignKeyExists("Comments", "PageId"))
            {
                this.AddForeignKey("Comments", "PageId", "Pages", "PageId");
            }

            if (!context.IsIndexExists("Comments", "PageId"))
            {
                this.CreateIndex("Comments", "PageId");
            }

            if (!context.IsForeignKeyExists("Pages", "LayoutId"))
            {
                this.AddForeignKey("Pages", "LayoutId", "Layouts", "LayoutId");
            }

            if (!context.IsIndexExists("Pages", "LayoutId"))
            {
                this.CreateIndex("Pages", "LayoutId");
            }

            if (!context.IsForeignKeyExists("TagPages", "Tag_TagId"))
            {
                this.AddForeignKey("TagPages", "Tag_TagId", "Tags", "TagId", cascadeDelete: true);
            }

            if (!context.IsIndexExists("TagPages", "Tag_TagId"))
            {
                this.CreateIndex("TagPages", "Tag_TagId");
            }

            if (!context.IsForeignKeyExists("TagPages", "Page_PageId"))
            {
                this.AddForeignKey("TagPages", "Page_PageId", "Pages", "PageId", cascadeDelete: true);
            }

            if (!context.IsIndexExists("TagPages", "Page_PageId"))
            {
                this.CreateIndex("TagPages", "Page_PageId");
            }
        }

        /// <summary>
        /// Operations to be performed during the downgrade process.
        /// </summary>
        public override void Down()
        {
            this.AddColumn("Pages", "LastChanged", c => c.DateTime(nullable: false));
            this.AddColumn("Comments", "LastChanged", c => c.DateTime(nullable: false));
            this.AlterColumn("SiteSettings", "SettingValue", c => c.String(maxLength: 4000));
            this.AlterColumn("SiteSettings", "SettingKey", c => c.String(maxLength: 4000));
            this.AlterColumn("Users", "PortalRole", c => c.String(maxLength: 4000));
            this.AlterColumn("Users", "Username", c => c.String(nullable: false, maxLength: 4000));
            this.DropColumn("SiteSettings", "LastModifiedUser");
            this.DropColumn("SiteSettings", "LastModified");
            this.DropColumn("Users", "LastModifiedUser");
            this.DropColumn("Users", "LastModified");
            this.DropColumn("Tags", "LastModifiedUser");
            this.DropColumn("Tags", "LastModified");
            this.DropColumn("Pages", "LastModifiedUser");
            this.DropColumn("Pages", "LastModified");
            this.DropColumn("Layouts", "LastModifiedUser");
            this.DropColumn("Layouts", "LastModified");
            this.DropColumn("Comments", "LastModifiedUser");
            this.DropColumn("Comments", "LastModified");
            this.DropTable("SiteSettingHistories");
            this.DropTable("PageHistories");
            this.DropTable("UserHistories");
            this.DropTable("TagHistories");
            this.DropTable("LayoutHistories");
            this.DropTable("CommentHistories");
        }

        private void ChangeColumnType(string table, string column, Func<ColumnBuilder, ColumnModel> b, string trim = null)
        {
            this.AddColumn(table, column + "1", b);
            this.Sql("UPDATE " + table + " SET " + column + "1 = " +
                (string.IsNullOrEmpty(trim) ? column : string.Format(trim, column)));
            this.DropColumn(table, column);
            this.AddColumn(table, column, b);
            this.Sql("UPDATE " + table + " SET " + column + " = " + (string.IsNullOrEmpty(trim) ? column + "1" : string.Format(trim, column + "1")));
            this.DropColumn(table, column + "1");
        }
    }
}
